package com.zhangyj.db;

import java.beans.IntrospectionException;
import java.beans.Introspector;
import java.beans.PropertyDescriptor;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Savepoint;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.ResultSetHandler;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.zhangyj.db.util.BaseDBUtil;
import com.zhangyj.db.util.PostgresqlUtil;

public class DBUtil {
	static Logger log = LoggerFactory.getLogger("dbutil");
	public static final int DATATYPE_INT = 0;
	public static final int DEFAULT_PAGESIZE = 50;
	public static final int DATATYPE_STRING = 1;
	static BaseDBUtil util = new PostgresqlUtil();

	public static String buildPagnationSql(String sql, int pageNo, int size, int tatalCount, String orderField, String orderTable, boolean isDesc) {
		return util.buildPagnationSql(sql, pageNo, size, tatalCount, orderField, orderTable, isDesc);
	}

	public static String buildPagnationSql(String sql, int pageNo, int size, int tatalCount, String orderField, boolean isDesc) {
		return buildPagnationSql(sql, pageNo, size, tatalCount, orderField, null, isDesc);
	}

	public static String buildPagnationSql(String sql, int pageNo, int tatalCount, String orderField, String orderTable, boolean isDesc) {
		return util.buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, orderTable, isDesc);
	}

	public static String buildPagnationSql(String sql, int pageNo, int tatalCount, String orderField, boolean isDesc) {
		return buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, null, isDesc);
	}

	public static String buildPagnationSqlAsc(String sql, int pageNo, int tatalCount, String orderTable, String orderField) {
		return buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, orderTable, false);
	}

	public static String buildPagnationSqlDesc(String sql, int pageNo, int tatalCount, String orderTable, String orderField) {
		return buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, orderTable, true);
	}

	public static String buildPagnationSqlAsc(String sql, int pageNo, int size, int tatalCount, String orderField) {
		return buildPagnationSql(sql, pageNo, size, tatalCount, orderField, null, false);
	}

	public static String buildPagnationSqlDesc(String sql, int pageNo, int size, int tatalCount, String orderField) {
		return buildPagnationSql(sql, pageNo, size, tatalCount, orderField, null, true);
	}

	public static String buildPagnationSqlAsc(String sql, int pageNo, int tatalCount, String orderField) {
		return buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, null, false);
	}

	public static String buildPagnationSqlDesc(String sql, int pageNo, int tatalCount, String orderField) {
		return buildPagnationSql(sql, pageNo, DEFAULT_PAGESIZE, tatalCount, orderField, null, true);
	}

	public static int getIntVal(Connection conn, String sql, Map<String, Object> params) throws SQLException {
		int returnVal = -1;
		ResultSetModel rsm = new ResultSetModel(conn, sql, params);
		rsm.open();
		if (rsm.next()) {
			returnVal = rsm.getInt(0);
		}
		rsm.close();
		return returnVal;
	}

	public static int getIntId(String tbname) throws SQLException {
		int index = 1;
		Connection conn = null;
		boolean autoCommit = true;
		Savepoint sp = null;
		try {
			conn = newConnection();
			autoCommit = conn.getAutoCommit();
			conn.setAutoCommit(false);
			sp = conn.setSavepoint();
			index = getIntId(conn, tbname);
			conn.commit();
		} catch (SQLException e) {
			if (conn != null && sp != null) {
				conn.rollback(sp);
			}
			throw e;
		} finally {
			if (conn != null) {
				conn.setAutoCommit(autoCommit);
				conn.close();
			}
		}
		return index;
	}

	public static int getIntId(Connection conn, String tbname) throws SQLException {
		int index = 1;
		String sql = "UPDATE b_syscode SET idindex=idindex where tbname=?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setString(1, tbname);
		int updateCount = ps.executeUpdate();
		ps.close();
		if (updateCount == 0) {
			sql = "INSERT INTO b_syscode (tbname,idindex) VALUES (?,?)";
			ps = conn.prepareStatement(sql);
			ps.setString(1, tbname);
			ps.setInt(2, index);
			ps.execute();
			ps.close();
			return index;
		}
		sql = "SELECT idindex FROM b_syscode WHERE tbname=?";
		ps = conn.prepareStatement(sql);
		ps.setString(1, tbname);
		ResultSet rst = ps.executeQuery();
		if (rst.next()) {
			index = rst.getInt(1);
		}
		rst.close();
		ps.close();
		index++;
		sql = "UPDATE b_syscode SET idindex=? WHERE tbname=?";
		ps = conn.prepareStatement(sql);
		ps.setInt(1, index);
		ps.setString(2, tbname);
		ps.executeUpdate();
		ps.close();
		return index;
	}

	public static String getStringId(String tbname, String prefix, int length) throws SQLException {
		String index = String.valueOf(getIntId(tbname));
		char[] cs = new char[length];
		int pLength = prefix.length();
		prefix.getChars(0, pLength, cs, 0);
		for (; pLength < length - index.length(); pLength++) {
			cs[pLength] = '0';
		}
		index.getChars(0, index.length(), cs, pLength);
		return new String(cs);
	}

	public static String getClob(String sql, Object key, int keyDataType) throws SQLException {
		Connection dbc = null;
		try {
			dbc = newConnection();
			return getClob(dbc, sql, key, keyDataType);
		} catch (SQLException e) {
			throw e;
		} finally {
			if (dbc != null) {
				dbc.close();
			}
		}
	}

	public static String getClob(Connection dbc, String sql, Object key, int keyDataType) throws SQLException {
		PreparedStatement ps = dbc.prepareStatement(sql);
		switch (keyDataType) {
		case (DATATYPE_INT):
			ps.setInt(1, (Integer) key);
			break;
		case (DATATYPE_STRING):
			ps.setString(1, (String) key);
			break;

		}
		ResultSet rst = ps.executeQuery();
		String result = null;
		if (rst.next()) {
			result = rst.getString(1);
		}
		rst.close();
		return result;
	}

	private static final ThreadLocal<Connection> threadConnection = new ThreadLocal<Connection>();

	public static Connection getConnection() throws SQLException {
		Connection conn = threadConnection.get();
		if (conn != null) {
			if (conn.isClosed() == false)
				return conn;
			else {
				threadConnection.remove();
			}
		}
		try {
			conn = newConnection();
			threadConnection.set(new Database(conn));
		} catch (Exception nfe) {
			throw new SQLException(nfe);
		}
		return conn;
	}

	protected static Connection newConnection() throws SQLException {
		try {
			Context ctx = new InitialContext();
			DataSource ds = (DataSource) ctx.lookup("java:comp/env/jdbc/postgres");
			return ds.getConnection();
		} catch (Exception nfe) {
			throw new SQLException(nfe);
		}
	}

	public static NamedPreparedStatement getNamedPreparedStatement(Connection conn, String sql) throws SQLException {
		return new NamedPreparedStatementImpl(conn, sql);
	}

	static public void begnTrans() throws SQLException {
		Connection conn = getConnection();
		conn.setAutoCommit(false);
	}

	static public void commit() throws SQLException {
		Connection conn = threadConnection.get();
		if (conn != null) {
			conn.commit();
			conn.setAutoCommit(true);
		}
	}

	static public void rollback() throws SQLException {
		Connection conn = threadConnection.get();
		if (conn != null) {
			conn.rollback();
			conn.setAutoCommit(true);
		}
	}

	static public void close() {
		Database conn = (Database) threadConnection.get();
		try {
			if (conn != null) {
				boolean autoCommit = conn.getAutoCommit();
				if (autoCommit == false) {
					conn.commit();
					log.warn("数据库事物没有提交，在关闭的时候系统提交");
				}
				threadConnection.remove();
			}
		} catch (SQLException e) {
			log.error("数据库关闭", e);
		} finally {
			if (conn != null) {
				try {
					conn.closeDB();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
		}
	}

	static public <T> T queryOne(Connection conn, String sql, Class<T> c, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		ResultSetHandler<T> bh ;
		if (isWrapClass(c)) {	
			bh = new ScalarHandler<T>();
		} else {
			 bh = new BeanHandler<T>(c);
		}
		T r = qr.query(conn, sql, bh, params);
		return r;
	}
	public static boolean isWrapClass(Class<?> clz) { 
        try { 
           return ((Class<?>) clz.getField("TYPE").get(null)).isPrimitive(); 
        } catch (Exception e) { 
            return false; 
        } 
    } 
	static public Map<String, Object> queryMap(Connection conn, String sql, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		MapHandler bh = new MapHandler();
		Map<String, Object> r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public <T> List<T> queryList(Connection conn, String sql, Class<T> c, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		BeanListHandler<T> bh = new BeanListHandler<T>(c);
		List<T> r = qr.query(conn, sql, bh, params);
		return r;
	}
	static public List<Map<String, Object>> queryMapList(Connection conn, String sql) throws SQLException {
		QueryRunner qr = new QueryRunner();
		MapListHandler bh = new MapListHandler();
		List<Map<String, Object>> r = qr.query(conn, sql, bh);
		return r;
	}
	static public List<Map<String, Object>> queryMapList(Connection conn, String sql, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		MapListHandler bh = new MapListHandler();
		List<Map<String, Object>> r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public int update(Connection conn, String sql, Object... params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		return qr.update(conn, sql, params);
	}

	static public int update(Connection conn, String sql, Object params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		return qr.update(conn, sql, params);
	}

	static public int[] batch(Connection conn, String sql, Object[][] params) throws SQLException {
		QueryRunner qr = new QueryRunner();
		return qr.batch(conn, sql, params);
	}

	static public <T> T queryOneFromNamedSQL(Connection conn, String sql, Class<T> c, Object bean) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[] params = fullParam(bean, paramList);
		QueryRunner qr = new QueryRunner();
		BeanHandler<T> bh = new BeanHandler<T>(c);
		T r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public Map<String, Object> queryMapFromNamedSQL(Connection conn, String sql, Object bean) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[] params = fullParam(bean, paramList);
		QueryRunner qr = new QueryRunner();
		MapHandler bh = new MapHandler();
		Map<String, Object> r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public <T> List<T> queryListFromNamedSQL(Connection conn, String sql, Class<T> c, Object bean) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[] params = fullParam(bean, paramList);
		QueryRunner qr = new QueryRunner();
		BeanListHandler<T> bh = new BeanListHandler<T>(c);
		List<T> r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public List<Map<String, Object>> queryMapListFromNamedSQL(Connection conn, String sql, Object bean) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[] params = fullParam(bean, paramList);
		QueryRunner qr = new QueryRunner();
		MapListHandler bh = new MapListHandler();
		List<Map<String, Object>> r = qr.query(conn, sql, bh, params);
		return r;
	}

	static public int updateFromNamedSQL(Connection conn, String sql, Object bean) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[] params = fullParam(bean, paramList);
		QueryRunner qr = new QueryRunner();
		return qr.update(conn, sql, params);
	}

	static public int[] batchFromNamedSQL(Connection conn, String sql, Object[] beans) throws SQLException {
		List<String> paramList = new ArrayList<String>();
		sql = parse(sql, paramList);
		Object[][] params = fullParams(beans, paramList);
		QueryRunner qr = new QueryRunner();
		return qr.batch(conn, sql, params);
	}

	private static final String parse(String query, List<String> paramList) {
		int length = query.length();
		StringBuffer parsedQuery = new StringBuffer(length);
		boolean inSingleQuote = false;
		boolean inDoubleQuote = false;
		for (int i = 0; i < length; i++) {
			char c = query.charAt(i);
			if (inSingleQuote) {
				if (c == '\'') {
					inSingleQuote = false;
				}
			} else if (inDoubleQuote) {
				if (c == '"') {
					inDoubleQuote = false;
				}
			} else {
				if (c == '\'') {
					inSingleQuote = true;
				} else if (c == '"') {
					inDoubleQuote = true;
				} else if (c == ':' && i + 1 < length && Character.isJavaIdentifierStart(query.charAt(i + 1))) {
					int j = i + 2;
					while (j < length && Character.isJavaIdentifierPart(query.charAt(j))) {
						j++;
					}
					String name = query.substring(i + 1, j);
					c = '?'; // replace the parameter with a question mark
					i += name.length(); // skip past the end if the parameter
					paramList.add(name);
				}
			}
			parsedQuery.append(c);
		}
		return parsedQuery.toString();
	}

	private static final Object[] fullParam(Object bean, List<String> paramList) {
		PropertyDescriptor[] descriptors;
		try {
			descriptors = Introspector.getBeanInfo(bean.getClass()).getPropertyDescriptors();
		} catch (IntrospectionException e) {
			throw new RuntimeException("Couldn't introspect bean " + bean.getClass().toString(), e);
		}
		Object[] result = new Object[paramList.size()];
		for (int i = 0; i < paramList.size(); i++) {
			String propertyName = paramList.get(i);
			if (propertyName == null) {
				throw new NullPointerException("propertyName can't be null: " + i);
			}
			boolean found = false;
			Object value = null;
			for (int j = 0; j < descriptors.length; j++) {
				PropertyDescriptor descriptor = descriptors[j];
				if (propertyName.equals(descriptor.getName())) {
					Method method = descriptor.getReadMethod();
					if (method == null) {
						throw new RuntimeException("No read method for bean property " + bean.getClass() + " " + descriptor.getName());
					}
					found = true;
					try {
						value = method.invoke(bean, new Object[0]);
					} catch (InvocationTargetException e) {
						throw new RuntimeException("Couldn't invoke method: " + method, e);
					} catch (IllegalArgumentException e) {
						throw new RuntimeException("Couldn't invoke method with 0 arguments: " + method, e);
					} catch (IllegalAccessException e) {
						throw new RuntimeException("Couldn't invoke method: " + method, e);
					}
					break;
				}
			}
			if (!found) {
				throw new RuntimeException("Couldn't find bean property: " + bean.getClass() + " " + propertyName);
			}
			result[i] = value;
		}
		return result;
	}

	private static final Object[][] fullParams(Object[] obj, List<String> paramList) {
		return null;
	}
	static public <T> T queryOne( String sql, Class<T> c, Object... params) throws SQLException {
		return queryOne(getConnection(), sql, c, params);
	}

	static public Map<String, Object> queryMap(String sql, Object... params) throws SQLException {
		return queryMap(getConnection(), sql, params);
	}

	static public <T> List<T> queryList(String sql, Class<T> c, Object... params) throws SQLException {
		return queryList(getConnection(), sql, c, params);
	}
	static public List<Map<String, Object>> queryMapList( String sql) throws SQLException {
		return queryMapList(getConnection(), sql);
	}
	static public List<Map<String, Object>> queryMapList( String sql, Object... params) throws SQLException {
		return queryMapList(getConnection(), sql, params);
	}

	static public int update(String sql, Object... params) throws SQLException {
		return update(getConnection(), sql, params);
	}

	static public int update(String sql, Object params) throws SQLException {
		return update(getConnection(), sql, params);
	}

	static public int[] batch(String sql, Object[][] params) throws SQLException {
		return batch(getConnection(), sql, params);
	}

	static public <T> T queryOneFromNamedSQL(String sql, Class<T> c, Object bean) throws SQLException {
		T r = queryOneFromNamedSQL(getConnection(), sql, c, bean);
		return r;
	}

	static public Map<String, Object> queryMapFromNamedSQL(String sql, Object bean) throws SQLException {
		Map<String, Object> r = queryMapFromNamedSQL(getConnection(), sql,bean);
		return r;
	}

	static public <T> List<T> queryListFromNamedSQL(String sql, Class<T> c, Object bean) throws SQLException {
		List<T> r = queryListFromNamedSQL(getConnection(), sql, c, bean);
		return r;
	}

	static public List<Map<String, Object>> queryMapListFromNamedSQL( String sql, Object bean) throws SQLException {
		
		List<Map<String, Object>> r = queryMapListFromNamedSQL(getConnection(), sql,bean);
		return r;
	}

	static public int updateFromNamedSQL( String sql, Object bean) throws SQLException {
		return updateFromNamedSQL(getConnection(), sql, bean);
	}

	static public int[] batchFromNamedSQL(String sql, Object[] beans) throws SQLException {
		return batchFromNamedSQL(getConnection(), sql, beans);
	}
	public static void main(String[] args) {
		String sql = "select * from b_ent";
		try {
			Connection conn = DBUtil.getConnection();
			List<Map<String, Object>> list = DBUtil.queryMapList(conn, sql);
			System.out.println(list);
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}
}
